Sorting Records Using ORDER BY in MySQL – Detailed Notes

Course: MySQL Training

Topic: Sorting Records Using ORDER BY

Course Link: MySQL Training Course

Demo Link: Register for a Course Demo

---

1. Introduction to Sorting Records in MySQL

Sorting is an important database operation that arranges retrieved records in a particular order. In MySQL, the ORDER BY clause is used to sort the result of a query based on one or more columns or expressions.

For example, a company may want to display employees alphabetically by name, arrange employees from the highest salary to the lowest salary, or show the newest orders first. An online shopping website may sort products by price, customer ratings, or product names.

Without an ORDER BY clause, MySQL does not guarantee that query results will appear in a particular order. Records may appear in a different order depending on the execution plan, indexes, storage, or other factors.

Example

SELECT employee_name, salary
FROM employees
ORDER BY salary DESC;

This query displays employee names and salaries, arranging the results from the highest salary to the lowest salary.

2. Learning Objectives

After completing this topic, you will be able to:

• Understand the purpose of the ORDER BY clause.
• Write queries to sort records in ascending and descending order.
• Sort records using numeric, text, date, and time columns.
• Sort records using multiple columns.
• Combine ORDER BY with WHERE, DISTINCT, GROUP BY, and HAVING.
• Use ORDER BY with aggregate functions and column aliases.
• Limit the number of sorted records using LIMIT.
• Understand NULL values and tie-breaking behavior during sorting.
• Identify common sorting errors and apply best practices.
• Understand how indexes and query execution plans can affect sorting performance.

3. What Is the ORDER BY Clause?

ORDER BY is a clause in a SELECT statement that specifies how the returned rows should be arranged. You can sort by one column, multiple columns, or expressions based on the selected data.

General Syntax

SELECT column1, column2
FROM table_name
ORDER BY column_name ASC;

To sort in descending order, use DESC:

SELECT column1, column2
FROM table_name
ORDER BY column_name DESC;

Syntax Explanation

Component	Description
SELECT	Specifies the columns to retrieve.
FROM	Identifies the table from which records are retrieved.
ORDER BY	Specifies the sorting column or expression.
column_name	The column used to arrange the records.
ASC	Sorts in ascending order.
DESC	Sorts in descending order.

4. Understanding ASC and DESC

MySQL supports two main sorting directions: ascending and descending.

4.1 Ascending Order (ASC)

ASC sorts values from lower to higher or from earlier to later, depending on the data type. For text values, sorting follows the applicable character set and collation rules.

SELECT employee_name, salary
FROM employees
ORDER BY salary ASC;

This query sorts employees from the lowest salary to the highest salary.

Examples of ascending order include:

• Numbers: 10, 20, 30, 40.
• Names: alphabetical order according to the column's collation.
• Dates: earliest date to latest date.
• Prices: lowest price to highest price.

4.2 Descending Order (DESC)

DESC reverses the ascending order, arranging values from higher to lower or from later to earlier.

SELECT employee_name, salary
FROM employees
ORDER BY salary DESC;

This query sorts employees from the highest salary to the lowest salary.

Examples of descending order include:

• Numbers: 40, 30, 20, 10.
• Dates: latest date to earliest date.
• Prices: highest price to lowest price.
• Names: reverse order according to the applicable collation.

4.3 Is ASC Required?

No. If you do not specify ASC or DESC, MySQL uses ascending order by default.

SELECT employee_name, salary
FROM employees
ORDER BY salary;

This query is equivalent to:

SELECT employee_name, salary
FROM employees
ORDER BY salary ASC;

5. Creating a Sample Database and Table

To practise ORDER BY, create a sample database containing employee information.

Step 1: Create a Database

CREATE DATABASE company_db;

Step 2: Select the Database

USE company_db;

Step 3: Create an Employees Table

CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    employee_name VARCHAR(100),
    department VARCHAR(50),
    age INT,
    salary DECIMAL(10,2),
    joining_date DATE
);

Step 4: Insert Sample Records

INSERT INTO employees
(employee_id, employee_name, department, age, salary, joining_date)
VALUES
(1, 'Aarav', 'IT', 24, 28000, '2023-01-15'),
(2, 'Priya', 'HR', 29, 42000, '2022-06-10'),
(3, 'Rahul', 'Finance', 35, 55000, '2021-03-20'),
(4, 'Sneha', 'IT', 27, 35000, '2024-02-05'),
(5, 'Karan', 'Sales', 41, 65000, '2020-11-12'),
(6, 'Meera', 'HR', 32, 48000, '2023-08-18'),
(7, 'Ishaan', 'IT', 30, 50000, '2022-12-01'),
(8, 'Ananya', 'Finance', 26, 31000, '2024-05-22'),
(9, 'Vikram', 'Sales', 38, 45000, '2021-09-14'),
(10, 'Riya', 'Marketing', 23, 25000, '2025-01-08');

Step 5: Display All Records

SELECT * FROM employees;

The table contains employee names, departments, ages, salaries, and joining dates. We will use these columns to understand different sorting techniques.

6. Sorting Records by a Single Column

The simplest use of ORDER BY is sorting records according to one column.

Example 1: Sort Employees by Name

SELECT employee_id, employee_name
FROM employees
ORDER BY employee_name ASC;

This query displays employee names in ascending alphabetical order according to the applicable collation.

Example 2: Sort Employees by Salary

SELECT employee_name, salary
FROM employees
ORDER BY salary ASC;

This query displays employees from the lowest salary to the highest salary.

Example 3: Sort Employees by Age

SELECT employee_name, age
FROM employees
ORDER BY age ASC;

This query places younger employees before older employees, with NULL handling determined by MySQL's sorting rules if the age column contains NULL values.

Example 4: Sort Employees by Department

SELECT employee_name, department
FROM employees
ORDER BY department ASC;

This query arranges employees according to their department values. Employees in the same department may appear in any order relative to one another unless another sorting column is specified.

7. Sorting Records in Descending Order

Use DESC when the required output should begin with the largest, latest, or highest-ranked values.

Example 1: Highest-Paid Employees First

SELECT employee_name, salary
FROM employees
ORDER BY salary DESC;

This query displays the highest salary first and the lowest salary last.

Example 2: Oldest Employees First

SELECT employee_name, age
FROM employees
ORDER BY age DESC;

This query sorts employee ages from highest to lowest.

Example 3: Newest Joiners First

SELECT employee_name, joining_date
FROM employees
ORDER BY joining_date DESC;

This query displays employees with the most recent joining dates first.

Example 4: Latest Orders First

SELECT order_id, order_date
FROM orders
ORDER BY order_date DESC;

This example assumes an orders table with order_id and order_date columns. It is useful for showing the latest transactions at the top of a report.

8. Sorting Records by Multiple Columns

MySQL allows you to sort by more than one column. The first column determines the primary order. If multiple records have the same value in that column, the next column determines their relative order.

Syntax

SELECT column1, column2
FROM table_name
ORDER BY column1 ASC, column2 DESC;

Example 1: Sort by Department and Salary

SELECT employee_name, department, salary
FROM employees
ORDER BY department ASC, salary DESC;

This query sorts departments in ascending order. Within each department, employees are arranged from highest salary to lowest salary.

Example 2: Sort by Department and Name

SELECT employee_name, department
FROM employees
ORDER BY department ASC, employee_name ASC;

Employees are grouped together by department in the sorted result, and names within each department are sorted alphabetically according to the applicable collation.

Example 3: Sort by Salary and Age

SELECT employee_name, salary, age
FROM employees
ORDER BY salary DESC, age ASC;

This query sorts salaries from highest to lowest. If two employees have the same salary, the younger employee appears first among those tied salaries.

Example 4: Sort by Date and Employee Name

SELECT employee_name, joining_date
FROM employees
ORDER BY joining_date DESC, employee_name ASC;

Employees with the latest joining dates appear first. If multiple employees share a joining date, their names determine the order within that date.

9. Understanding Sorting Priority

When multiple columns are specified in ORDER BY, their priority matters.

Sorting Expression	Priority	Explanation
ORDER BY department, salary	Department first	Salary sorts records within the same department.
ORDER BY salary DESC, age ASC	Salary first	Age sorts employees who have equal salaries.
ORDER BY joining_date DESC, employee_name ASC	Joining date first	Name sorts employees who share a joining date.

Important: A secondary column does not override the primary sort. It only determines order among rows tied on the earlier sorting expression.

10. Sorting Records with WHERE

The WHERE clause filters records, while ORDER BY sorts the records that remain. You can combine them to retrieve only the required data and arrange it in a meaningful order.

Example 1: Filter IT Employees and Sort by Salary

SELECT employee_name, department, salary
FROM employees
WHERE department = 'IT'
ORDER BY salary DESC;

This query retrieves only IT employees and sorts them from the highest salary to the lowest salary.

Example 2: Find Employees Earning More Than 35,000

SELECT employee_name, salary
FROM employees
WHERE salary > 35000
ORDER BY salary ASC;

This query filters out employees earning 35,000 or less and sorts the remaining employees by salary in ascending order.

Example 3: Filter a Salary Range and Sort Names

SELECT employee_name, salary
FROM employees
WHERE salary BETWEEN 30000 AND 50000
ORDER BY employee_name ASC;

This query retrieves employees whose salaries fall within the inclusive range and sorts their names alphabetically.

11. Sorting Records with DISTINCT

DISTINCT removes duplicate values from the result. ORDER BY can then arrange the remaining unique values.

Example 1: Display Unique Departments

SELECT DISTINCT department
FROM employees
ORDER BY department ASC;

This query returns each distinct department once and sorts the department names in ascending order.

Example 2: Display Unique Ages from Highest to Lowest

SELECT DISTINCT age
FROM employees
ORDER BY age DESC;

This query returns unique age values sorted from highest to lowest. If age contains NULL, the NULL value appears only once in the distinct result.

12. Sorting Records with LIMIT

LIMIT restricts how many rows are returned. When combined with ORDER BY, it is useful for retrieving the top or bottom records according to a specified sorting criterion.

Example 1: Find the Five Highest-Paid Employees

SELECT employee_name, salary
FROM employees
ORDER BY salary DESC
LIMIT 5;

This query returns up to five employees with the highest salaries.

Example 2: Find the Three Lowest-Paid Employees

SELECT employee_name, salary
FROM employees
ORDER BY salary ASC
LIMIT 3;

This query returns up to three employees with the lowest salaries.

Example 3: Retrieve the First Five Names Alphabetically

SELECT employee_name
FROM employees
ORDER BY employee_name ASC
LIMIT 5;

This query returns the first five records in the specified alphabetical order.

Example 4: Use LIMIT with OFFSET

SELECT employee_name, salary
FROM employees
ORDER BY salary DESC
LIMIT 5 OFFSET 5;

This query skips the first five sorted records and returns up to the next five. OFFSET is zero-based: OFFSET 5 starts at the sixth row of the sorted result.

For consistent pagination, use a unique tie-breaker such as employee_id in the ORDER BY clause so that records with equal salaries do not move unpredictably between pages.

13. Sorting Records with GROUP BY

GROUP BY groups records that share a value. ORDER BY can then sort the grouped output, often based on an aggregate result such as COUNT() or AVG().

Example 1: Count Employees in Each Department

SELECT department, COUNT(*) AS employee_count
FROM employees
GROUP BY department
ORDER BY employee_count DESC;

This query counts employees in each department and sorts departments from the largest employee count to the smallest.

Example 2: Sort Departments by Average Salary

SELECT department, AVG(salary) AS average_salary
FROM employees
GROUP BY department
ORDER BY average_salary DESC;

This query calculates the average salary for each department and displays departments with the highest average first.

Example 3: Sort by Department Name

SELECT department, COUNT(*) AS employee_count
FROM employees
GROUP BY department
ORDER BY department ASC;

This query groups employees by department and sorts the grouped rows by department name.

Important: GROUP BY does not guarantee the final order of the results. Use ORDER BY whenever a particular output order is required.

14. Sorting Records with HAVING

HAVING filters grouped results, often using aggregate conditions. ORDER BY can then arrange the groups that remain.

Example: Departments with More Than One Employee

SELECT department, COUNT(*) AS employee_count
FROM employees
GROUP BY department
HAVING COUNT(*) > 1
ORDER BY employee_count DESC;

This query groups employees by department, keeps departments with more than one employee, and sorts the remaining departments by employee count in descending order.

15. Sorting Records Using Aggregate Functions

Aggregate functions calculate a value from multiple rows. You can sort query results using these calculated values.

Example 1: Departments with the Highest Total Salary

SELECT department, SUM(salary) AS total_salary
FROM employees
GROUP BY department
ORDER BY total_salary DESC;

This query calculates the total salary for each department and sorts departments by total salary.

Example 2: Sort by Average Age

SELECT department, AVG(age) AS average_age
FROM employees
GROUP BY department
ORDER BY average_age ASC;

This query sorts departments by their average employee age from lowest to highest.

Example 3: Sort by Employee Count

SELECT department, COUNT(*) AS total_employees
FROM employees
GROUP BY department
ORDER BY total_employees DESC;

The department with the largest employee count appears first, followed by departments with fewer employees.

16. Sorting by Column Aliases

A column alias gives a column or expression a temporary name in the query result. MySQL allows an alias from the SELECT list to be referenced in ORDER BY.

Example 1: Sort by a Calculated Salary

SELECT employee_name, salary * 12 AS annual_salary
FROM employees
ORDER BY annual_salary DESC;

This query calculates annual salary and sorts employees from highest to lowest annual salary.

Example 2: Sort by a Concatenated Name

SELECT CONCAT(employee_name, ' - ', department) AS employee_details
FROM employees
ORDER BY employee_details ASC;

This query combines employee names and department names into one output column and sorts the resulting text.

Example 3: Sort by an Aggregate Alias

SELECT department, AVG(salary) AS average_salary
FROM employees
GROUP BY department
ORDER BY average_salary DESC;

Using aliases can improve readability, especially when sorting by calculated expressions or aggregate results.

17. Sorting by Expressions

ORDER BY can sort records using expressions rather than only existing column names.

Example 1: Sort by Annual Salary

SELECT employee_name, salary
FROM employees
ORDER BY salary * 12 DESC;

This query sorts employees by their calculated annual salary, assuming salary represents a monthly amount.

Example 2: Sort by Name Length

SELECT employee_name
FROM employees
ORDER BY CHAR_LENGTH(employee_name) ASC;

This query places shorter employee names before longer employee names. Names of the same length may appear in any relative order unless an additional sorting expression is supplied.

Example 3: Sort by a Conditional Expression

SELECT employee_name, department, salary
FROM employees
ORDER BY
    CASE
        WHEN department = 'IT' THEN 1
        WHEN department = 'HR' THEN 2
        WHEN department = 'Finance' THEN 3
        ELSE 4
    END ASC,
    employee_name ASC;

This query sorts departments in a custom order: IT first, HR second, Finance third, and all other departments afterward. Employee names are then sorted within each department priority.

18. Sorting Records with NULL Values

NULL represents a missing or unknown value. In MySQL, NULL values sort before non-NULL values in ascending order and after non-NULL values in descending order.

Example 1: Sort by Salary in Ascending Order

SELECT employee_name, salary
FROM employees
ORDER BY salary ASC;

If salary contains NULL values, those rows appear before rows with non-NULL salaries in ascending order.

Example 2: Sort by Salary in Descending Order

SELECT employee_name, salary
FROM employees
ORDER BY salary DESC;

If salary contains NULL values, those rows appear after rows with non-NULL salaries in descending order.

Example 3: Put NULL Salaries Last in Ascending Order

SELECT employee_name, salary
FROM employees
ORDER BY salary IS NULL ASC, salary ASC;

The expression salary IS NULL evaluates to 0 for non-NULL values and 1 for NULL values. Sorting it in ascending order places non-NULL salaries first and NULL salaries last.

19. Sorting Records by Date and Time

ORDER BY is frequently used with date and time columns to display records chronologically or in reverse chronological order.

Example 1: Oldest Joining Dates First

SELECT employee_name, joining_date
FROM employees
ORDER BY joining_date ASC;

This query displays the earliest joining dates first.

Example 2: Most Recent Joining Dates First

SELECT employee_name, joining_date
FROM employees
ORDER BY joining_date DESC;

This query displays the latest joining dates first.

Example 3: Sort Orders by Date and Time

SELECT order_id, order_datetime
FROM orders
ORDER BY order_datetime DESC;

This example assumes the orders table contains a DATETIME column named order_datetime. It displays the most recent order timestamps first.

Best Practice

Store date and time information in suitable DATE, DATETIME, or TIMESTAMP columns instead of storing it as arbitrary text. Proper data types allow MySQL to sort chronologically rather than alphabetically.

20. Sorting Numeric and Text Values Correctly

Sorting results depends on the column's data type and comparison rules.

Numeric Sorting

SELECT product_name, price
FROM products
ORDER BY price ASC;

If price is stored as a numeric type, MySQL sorts it numerically, from the lowest price to the highest.

Text Sorting

SELECT product_name
FROM products
ORDER BY product_name ASC;

Text values are sorted according to the applicable character set and collation rules.

Common Problem: Numbers Stored as Text

If numeric values are stored in a text column, their order may not match numeric order. For example, lexical sorting can place the string '100' before '20'.

SELECT item_code, quantity_text
FROM inventory
ORDER BY quantity_text ASC;

If quantity_text contains numeric values stored as strings, the result may be sorted lexically rather than numerically. The preferred solution is to use a suitable numeric data type. Casting may help for a specific query, but invalid text values can produce conversion problems.

21. Sorting by Column Position

MySQL supports sorting by the position of a column in the SELECT list, but this approach is discouraged because it can make queries harder to read and maintain.

Example

SELECT employee_name, department, salary
FROM employees
ORDER BY 3 DESC;

Here, 3 refers to the third selected expression, which is salary. The query therefore sorts salaries from highest to lowest.

Recommended approach: Use the column name instead.

SELECT employee_name, department, salary
FROM employees
ORDER BY salary DESC;

Using column names makes the intended sorting logic clearer and avoids errors when the SELECT list changes.

22. Sorting Joined Tables

ORDER BY can sort data retrieved from multiple tables using JOIN. When columns have the same name in different tables, qualify them with the table name or alias to avoid ambiguity.

Example: Sort Orders by Customer Name

SELECT c.customer_name, o.order_id, o.order_date
FROM customers AS c
JOIN orders AS o
    ON c.customer_id = o.customer_id
ORDER BY c.customer_name ASC, o.order_date DESC;

This example assumes customers and orders tables linked by customer_id. It sorts customers alphabetically and displays each customer's latest orders first.

Example: Sort Products by Category and Price

SELECT c.category_name, p.product_name, p.price
FROM categories AS c
JOIN products AS p
    ON c.category_id = p.category_id
ORDER BY c.category_name ASC, p.price DESC;

This query sorts products by category and places the most expensive products first within each category.

23. Sorting Records with Subqueries

ORDER BY can be used in a query that contains a subquery. The outer query's ORDER BY determines the order of the final result.

Example: Sort Employees Above the Average Salary

SELECT employee_name, salary
FROM employees
WHERE salary > (
    SELECT AVG(salary)
    FROM employees
)
ORDER BY salary DESC;

This query retrieves employees whose salaries are higher than the average salary across the table, then sorts them from highest to lowest salary.

Important Note

When the order of the final output matters, put ORDER BY in the outermost SELECT statement. An ORDER BY inside a subquery does not generally guarantee the order of the outer query's results.

24. Sorting with LIMIT for Top-N Reports

One common use of ORDER BY is to create top-N or bottom-N reports. These reports display a limited number of records ranked according to a selected column.

Example 1: Top Three Salaries

SELECT employee_name, salary
FROM employees
ORDER BY salary DESC, employee_id ASC
LIMIT 3;

This query returns the three highest salary records. employee_id provides a deterministic tie-breaker when salaries are equal.

Example 2: Lowest Three Salaries

SELECT employee_name, salary
FROM employees
ORDER BY salary ASC, employee_id ASC
LIMIT 3;

This query returns the three lowest salary records, with employee_id resolving ties.

Example 3: Top Five Most Recent Orders

SELECT order_id, order_date
FROM orders
ORDER BY order_date DESC, order_id DESC
LIMIT 5;

This query returns up to five recent orders and uses order_id as an additional sorting key for records sharing the same order_date.

25. Understanding Stable and Deterministic Sorting

When multiple records have the same value in the specified ORDER BY column, MySQL is free to return those tied records in any relative order unless more sorting criteria are specified.

Example Without a Tie-Breaker

SELECT employee_name, salary
FROM employees
ORDER BY salary DESC;

Employees with equal salaries have no guaranteed relative order.

Example With a Tie-Breaker

SELECT employee_id, employee_name, salary
FROM employees
ORDER BY salary DESC, employee_id ASC;

This query sorts by salary first and then by employee_id. Because employee_id is unique in the sample table, the result has a deterministic order for that dataset.

This is especially important when using LIMIT and OFFSET for pagination. A unique tie-breaker helps avoid unpredictable ordering among rows that share the primary sort value.

26. Performance Optimization for ORDER BY

Sorting can require additional processing, particularly when a large number of rows must be sorted. MySQL may use an index to satisfy ORDER BY or perform an additional sorting operation when an appropriate index cannot be used.

26.1 Use Suitable Indexes

An index may help MySQL return records in the requested order without a separate sort. Whether an index is used depends on the query, the index structure, the WHERE conditions, the selected columns, and the optimizer's cost estimates.

CREATE INDEX idx_employee_salary
ON employees (salary);

This creates an index on salary. It may help some queries that filter or sort by salary, but an index does not guarantee that every ORDER BY query will avoid sorting.

26.2 Use Composite Indexes for Common Multi-Column Sorting

CREATE INDEX idx_department_salary
ON employees (department, salary);

This index may help queries that filter or sort by department and salary in compatible ways. The order of columns in a composite index matters, and the optimizer decides whether the index is useful for a particular query.

26.3 Avoid Sorting Unnecessary Columns

Sort by the columns required by the task rather than adding unnecessary expressions or complex calculations. Simpler sorting requirements can be easier for the optimizer to execute efficiently.

26.4 Use LIMIT When Only a Few Rows Are Needed

SELECT employee_name, salary
FROM employees
ORDER BY salary DESC
LIMIT 10;

This query requests only ten rows. MySQL may optimize a top-N query, although the amount of work depends on the available indexes and execution plan.

26.5 Inspect the Execution Plan

EXPLAIN
SELECT employee_name, salary
FROM employees
ORDER BY salary DESC;

EXPLAIN helps you inspect the query plan. If the Extra column shows Using filesort, MySQL is performing an additional sorting operation rather than satisfying the requested order entirely through an index. Filesort does not necessarily mean that a physical disk file is used; the operation may be handled in memory.

For detailed guidance, refer to the official MySQL ORDER BY Optimization documentation.

27. Common Mistakes When Using ORDER BY

Mistake 1: Expecting Results to Be Automatically Sorted

Problem: A query does not include ORDER BY, but the developer expects rows to appear alphabetically or by ID.

Solution: Explicitly specify the required sorting order.

SELECT employee_name
FROM employees
ORDER BY employee_name ASC;

Mistake 2: Using DESC When ASC Is Required

Problem: Records appear in the opposite direction from the intended order.

Solution: Check whether the requirement asks for lowest-to-highest or highest-to-lowest sorting.

Mistake 3: Sorting by the Wrong Column

Problem: The result is sorted by age when the requirement asks for salary.

Solution: Verify the column names used in ORDER BY.

Mistake 4: Forgetting Secondary Sorting Criteria

Problem: Records with equal values appear in an unexpected relative order.

Solution: Add a secondary column, preferably a unique key when deterministic ordering is required.

Mistake 5: Confusing GROUP BY with ORDER BY

Problem: A developer expects GROUP BY to guarantee the final display order.

Solution: Use GROUP BY to form groups and ORDER BY to specify the output order.

Mistake 6: Sorting Numeric Data Stored as Text

Problem: Values may be sorted lexically rather than numerically.

Solution: Store numeric values in appropriate numeric data types.

Mistake 7: Using an Alias Incorrectly

Problem: A sorting expression refers to a confusing or ambiguous column name.

Solution: Use clear aliases and qualify column names where necessary.

Mistake 8: Assuming NULL Values Always Appear Last

Problem: NULL values appear before non-NULL values in ascending sorting.

Solution: Use an expression such as salary IS NULL ASC when you specifically need NULL values last.

Mistake 9: Using LIMIT Without ORDER BY

Problem: A query returns a limited set of records, but the same records are not guaranteed to appear in the same order on every execution.

Solution: Use ORDER BY with a suitable tie-breaker when a specific subset or repeatable pagination is required.

28. Practical Project: Employee Sorting and Reporting System

Project Objective: Build a set of SQL queries that sort employee records for HR reporting, salary analysis, department summaries, and recent-joiner reports.

Task 1: Display Employees Alphabetically

SELECT employee_id, employee_name
FROM employees
ORDER BY employee_name ASC;

Task 2: Display the Highest-Paid Employees

SELECT employee_name, salary
FROM employees
ORDER BY salary DESC;

Task 3: Display the Five Lowest-Paid Employees

SELECT employee_name, salary
FROM employees
ORDER BY salary ASC, employee_id ASC
LIMIT 5;

Task 4: Sort by Department and Salary

SELECT employee_name, department, salary
FROM employees
ORDER BY department ASC, salary DESC;

Task 5: Display Recent Joiners

SELECT employee_name, joining_date
FROM employees
ORDER BY joining_date DESC, employee_id DESC
LIMIT 5;

Task 6: Count Employees in Each Department

SELECT department, COUNT(*) AS employee_count
FROM employees
GROUP BY department
ORDER BY employee_count DESC;

Task 7: Calculate Average Salary by Department

SELECT department, AVG(salary) AS average_salary
FROM employees
GROUP BY department
ORDER BY average_salary DESC;

Task 8: Filter and Sort Employees

SELECT employee_name, department, salary
FROM employees
WHERE salary BETWEEN 30000 AND 50000
ORDER BY salary DESC, employee_id ASC;

Expected Learning Outcome

After completing this project, you should be able to sort records by multiple columns, filter and sort data together, create top-N reports, and arrange grouped aggregate results for practical business reporting.

29. Interview Questions and Answers

Q1. What is ORDER BY in MySQL?

ORDER BY is a clause used to arrange query results according to one or more columns or expressions.

Q2. What is the difference between ASC and DESC?

ASC sorts in ascending order, while DESC sorts in descending order. ASC is the default direction.

Q3. How do you sort employees by salary from highest to lowest?

SELECT employee_name, salary
FROM employees
ORDER BY salary DESC;

Q4. Can ORDER BY sort multiple columns?

Yes. You can specify multiple columns, each with its own sorting direction.

SELECT employee_name, department, salary
FROM employees
ORDER BY department ASC, salary DESC;

Q5. What happens when two rows have the same sorting value?

Their relative order is not guaranteed unless another sorting expression is specified.

Q6. Can ORDER BY be used with WHERE?

Yes. WHERE filters the rows, and ORDER BY arranges the rows that remain.

Q7. Can ORDER BY be used with GROUP BY?

Yes. GROUP BY forms groups, and ORDER BY sorts the grouped result.

Q8. Can ORDER BY use a column alias?

Yes. A column alias defined in the SELECT list can be referenced by ORDER BY.

Q9. What is the purpose of LIMIT with ORDER BY?

LIMIT restricts the number of returned rows after the requested ordering has been applied. It is useful for top-N reports and pagination.

Q10. Does GROUP BY guarantee sorted output?

No. Use ORDER BY whenever a specific output order is required.

Q11. How does MySQL sort NULL values?

NULL values sort before non-NULL values in ascending order and after non-NULL values in descending order.

Q12. Can ORDER BY sort a calculated expression?

Yes. For example, you can sort by salary * 12 to arrange employees by calculated annual salary.

Q13. Why should column positions be avoided in ORDER BY?

Although MySQL supports sorting by select-list positions, using explicit column names is clearer and less likely to break when the SELECT list changes.

Q14. What does Using filesort mean in EXPLAIN?

It indicates that MySQL performs an additional sorting operation instead of satisfying the requested order entirely through an index. It does not necessarily mean that a disk file is used.

Q15. How can you make sorting more efficient?

Use appropriate indexes where useful, keep sorting requirements simple, limit rows when appropriate, and inspect execution plans with EXPLAIN.

30. Practice Exercises

Use the sample employees table to complete these exercises.

1. Display all employees in alphabetical order by name.
2. Display employees from the highest salary to the lowest salary.
3. Sort employees by age in ascending order.
4. Display employees with the latest joining dates first.
5. Sort employees by department and then salary in descending order.
6. Display the three employees with the highest salaries.
7. Find unique department names and sort them alphabetically.
8. Count employees in each department and sort by employee count.
9. Calculate average salary by department and sort from highest to lowest.
10. Display employees earning between 30,000 and 50,000, sorted by salary.
11. Explain why ORDER BY should be used with LIMIT when selecting the top five records.
12. Write a query that sorts by salary descending and employee_id ascending.

31. Practice Exercise Solutions

Solution 1: Sort by Employee Name

SELECT employee_name
FROM employees
ORDER BY employee_name ASC;

Solution 2: Sort by Salary Descending

SELECT employee_name, salary
FROM employees
ORDER BY salary DESC;

Solution 3: Sort by Age Ascending

SELECT employee_name, age
FROM employees
ORDER BY age ASC;

Solution 4: Latest Joining Dates First

SELECT employee_name, joining_date
FROM employees
ORDER BY joining_date DESC;

Solution 5: Sort by Department and Salary

SELECT employee_name, department, salary
FROM employees
ORDER BY department ASC, salary DESC;

Solution 6: Top Three Salaries

SELECT employee_name, salary
FROM employees
ORDER BY salary DESC, employee_id ASC
LIMIT 3;

Solution 7: Unique Departments

SELECT DISTINCT department
FROM employees
ORDER BY department ASC;

Solution 8: Employee Count by Department

SELECT department, COUNT(*) AS employee_count
FROM employees
GROUP BY department
ORDER BY employee_count DESC;

Solution 9: Average Salary by Department

SELECT department, AVG(salary) AS average_salary
FROM employees
GROUP BY department
ORDER BY average_salary DESC;

Solution 10: Filter and Sort Salary

SELECT employee_name, salary
FROM employees
WHERE salary BETWEEN 30000 AND 50000
ORDER BY salary ASC;

Solution 11: Why Use ORDER BY with LIMIT?

ORDER BY defines which records rank first. LIMIT then restricts the output to the requested number of rows. Without ORDER BY, the selected rows are not guaranteed to represent the highest- or lowest-ranked records.

Solution 12: Sort by Salary and Employee ID

SELECT employee_id, employee_name, salary
FROM employees
ORDER BY salary DESC, employee_id ASC;

32. Quick Revision Checklist

• ORDER BY arranges the rows returned by a query.
• ASC sorts in ascending order and is the default.
• DESC sorts in descending order.
• Multiple columns can be used to establish sorting priority.
• WHERE filters rows before the final result is sorted.
• GROUP BY groups rows, while ORDER BY sorts the output.
• ORDER BY can use column names, aliases, and expressions.
• LIMIT restricts the number of returned rows.
• Use a unique tie-breaker for predictable sorting and pagination.
• NULL values appear first in ascending order and last in descending order.
• Appropriate indexes may help MySQL avoid an additional sorting operation.
• Use EXPLAIN to inspect the query execution plan.
• Do not assume results have a guaranteed order without ORDER BY.

33. Official Documentation and Further Learning

Refer to the following official resources for additional information about sorting and query optimization in MySQL.

• MySQL SELECT Statement Documentation – Learn about SELECT syntax, ORDER BY, LIMIT, aliases, and query clauses.
• MySQL ORDER BY Optimization – Understand index-based sorting and filesort operations.
• MySQL EXPLAIN Statement – Learn how to inspect query execution plans.

34. Conclusion

The ORDER BY clause is an essential part of MySQL because it allows query results to be arranged in a meaningful and predictable order. You can sort by names, salaries, ages, dates, calculated expressions, or aggregate results using ASC and DESC.

ORDER BY becomes even more powerful when combined with WHERE, DISTINCT, GROUP BY, HAVING, and LIMIT. Multiple sorting columns allow you to define priorities, while unique tie-breakers help make pagination more consistent. For large datasets, suitable indexes and query-plan inspection can help improve performance.

Practise the examples in these notes to develop confidence in sorting database records. To continue learning, explore the MySQL Training Course or register for a course demo.